using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;
using SharpNote.Core.Data;

namespace SharpNote.Core.Data
{
	public class DbObjectMappingHelper
	{
		#region Field & Properties



		#endregion

		#region Constructors

		public DbObjectMappingHelper ()
		{
		}

		#endregion

		#region Methods

		public static string GenerateEntityClassCode (string sql, string tableName, string className)
		{
			if (string.IsNullOrEmpty (sql)) {
				return string.Empty;
			}

			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();
			DataTable table = das.Query (sql);
			if (table == null) {
				return string.Empty;
			}

			StringBuilder fieldsCode = new StringBuilder ();

			fieldsCode.AppendLine ("    #region");
			fieldsCode.AppendLine ();
			foreach (DataColumn col in table.Columns) {
				fieldsCode.AppendLine ("        private " + col.DataType.FullName + " _" + ToFirstCharLower (col.ColumnName) + ";");
				fieldsCode.AppendLine ();
				fieldsCode.AppendLine (string.Format ("        [FieldMapping({0}, {1}, {2}, {2})]", (col.Unique ? "true" : "false"), GetDbTypeFullname (col.DataType), (col.AutoIncrement ? "true" : "false"), (col.Unique ? "false" : "true")));
				fieldsCode.AppendLine ("        public " + col.DataType.FullName + " " + col.ColumnName);
				fieldsCode.AppendLine ("        {");
				fieldsCode.AppendLine ("             get { return " + " _" + ToFirstCharLower (col.ColumnName) + "; }");
				fieldsCode.AppendLine ("             set { " + " _" + ToFirstCharLower (col.ColumnName) + " = value; }");
				fieldsCode.AppendLine ("        }");
				fieldsCode.AppendLine ();
			}
			fieldsCode.AppendLine ();
			fieldsCode.AppendLine ("    #endregion");

			StringBuilder code = new StringBuilder ();
			code.AppendLine ("[TableInfoAttribute(\"" + tableName + "\")]");
			code.AppendLine ("public class " + className + ": DbMappingObjectBase");
			code.AppendLine ("{");
			code.AppendLine (fieldsCode.ToString ());
			code.AppendLine ("        #region Constructors");
			code.AppendLine ();
			code.AppendLine ("            public " + className + "() { }");
			code.AppendLine ();
			code.AppendLine ("        #endregion");
			code.AppendLine ();
			code.AppendLine ("}");

			return code.ToString ();
		}

		/// <summary>
		/// 返回指定字符中的首字母小写的字符串。
		/// </summary>
		/// <param name="str"></param>
		/// <returns></returns>
		private static string ToFirstCharLower (string str)
		{
			if (string.IsNullOrEmpty (str)) {
				return string.Empty;
			} else {
				char[] array = str.ToCharArray ();
				array[0] = char.ToLower (array[0]);

				return new string (array);
			}
		}

		private static string GetDbTypeFullname (Type type)
		{
			if (type == typeof(string)) {
				return typeof(DbType).FullName + "." + DbType.String.ToString ();
			} else if (type == typeof(int)) {
				return typeof(DbType).FullName + "." + DbType.Int32.ToString ();
			} else if (type == typeof(Int64)) {
				return typeof(DbType).FullName + "." + DbType.Int64.ToString ();
			} else if (type == typeof(DateTime)) {
				return typeof(DbType).FullName + "." + DbType.DateTime.ToString ();
			}else if(type == typeof(byte[])){
			    return typeof(DbType).FullName + "." + DbType.Binary.ToString();	
			}
			else {
				return typeof(DbType).FullName + "." + DbType.String.ToString ();
			}
		}

		private static void FillProperties<T> (T instance, DataRow row)
		{
			if (instance == null || row == null) {
				return;
			}

			PropertyInfo[] props = instance.GetType ().GetProperties ();
			if (props == null || props.Length == 0) {
				return;
			}

			foreach (PropertyInfo prop in props) {
				foreach (DataColumn col in row.Table.Columns) {
					if (col.ColumnName.Equals (prop.Name, StringComparison.CurrentCultureIgnoreCase)) {
						prop.SetValue (instance, (row[col] != DBNull.Value ? row[col] : null), null);
					}
				}
			}
		}

		/// <summary>
		/// 使用查询结果集中的第一行列值对应填充到指定类实例名称相同的属性。
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql">返回结果集的 Sql 语句</param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public static T Query<T> (string sql, out bool isEmpty, 
		                          params DbParameter[] parameters) where T : new()
		{
			isEmpty = true;
			T result = new T ();
			if (string.IsNullOrEmpty (sql)) {
				return result;
			}

			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();
			DataTable table = das.Query (sql, CommandType.Text, parameters);
			if (table == null || table.Rows.Count == 0) {
				return result;
			}

			FillProperties (result, table.Rows[0]);
			isEmpty = false;
			
			return result;
		}

		/// <summary>
		/// 将指定 DataTable 行集转换为对象集合。
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		public static List<T> QueryObjectList<T> (string sql, params DbParameter[] parameters) where T : new()
		{
			List<T> result = new List<T> ();

			if (string.IsNullOrEmpty (sql)) {
				return result;
			}

			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();
			DataTable table = das.Query (sql, CommandType.Text, parameters);
			if (table == null || table.Rows.Count == 0) {
				return result;
			}

			foreach (DataRow row in table.Rows) {
				T instance = new T ();
				FillProperties (instance, row);

				result.Add (instance);
			}

			return result;
		}

		/*
        /// <summary>
        /// 将指定 DataTable 行集转换为对象集合。(支持分页, 起始页码为 1)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> QueryObjectList<T>(string sql,
            long rowCountPerpage, long pageNumber, params DbParameter[] parameters) where T : new()
        {
            List<T> result = new List<T>();

            if (string.IsNullOrEmpty(sql)) { return result; }

            DataAccessService das = DataAccessService.GetDataAccessServiceInstance();
            DataTable table = das.Query(sql, rowCountPerpage, pageNumber, CommandType.Text, parameters);
            if (table == null || table.Rows.Count == 0) { return result; }

            foreach (DataRow row in table.Rows)
            {
                T instance = new T();
                FillProperties(instance, row);

                result.Add(instance);
            }

            return result;
        }*/

		/// <summary>
		/// 从数据库中删除指定对象。
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="instance"></param>
		/// <returns></returns>
		public static bool Remove<T> (T instance) where T : new()
		{
			if (instance == null) {
				return false;
			}

			TableInfoAttribute[] attributes = instance.GetType ().GetCustomAttributes (typeof(TableInfoAttribute), true) as TableInfoAttribute[];
			if (attributes == null || attributes.Length == 0) {
				return false;
			}

			string tableName = attributes[0].TableName;
			if (string.IsNullOrEmpty (tableName)) {
				return false;
			}

			StringBuilder sql = new StringBuilder ();
			List<DbParameter> parameters = new List<DbParameter> ();
			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();

			PropertyInfo[] props = instance.GetType ().GetProperties (BindingFlags.Instance | BindingFlags.Public);
			if (props == null || props.Length == 0) {
				return false;
			}

			FieldMappingAttribute[] fmAttributes = null;
			foreach (PropertyInfo prop in props) {
				fmAttributes = prop.GetCustomAttributes (typeof(FieldMappingAttribute), true) as FieldMappingAttribute[];
				if (fmAttributes == null || fmAttributes.Length == 0) {
					return false;
				}

				if (!fmAttributes[0].IsPrimaryKey) {
					continue;
				}

				sql.AppendLine (string.Format ("{0} = @{0}", prop.Name));
				parameters.Add (DataAccessService.CreateDbParameter ("@" + prop.Name, fmAttributes[0].DbType, prop.GetValue (instance, null)));
			}

			if (sql.Length == 0) {
				return false;
			}
			string sqlString = "Delete from " + tableName + " Where " + sql.ToString ();

			return das.ExecuteNonQuery (sqlString, CommandType.Text, parameters.ToArray ()) > 0;
		}

		public static bool Create<T> (T instance) where T : new()
		{
			if (instance == null) {
				return false;
			}

			TableInfoAttribute[] attributes = instance.GetType ().GetCustomAttributes (typeof(TableInfoAttribute), true) as TableInfoAttribute[];
			if (attributes == null || attributes.Length == 0) {
				return false;
			}

			string tableName = attributes[0].TableName;
			if (string.IsNullOrEmpty (tableName)) {
				return false;
			}

			StringBuilder sql = new StringBuilder ();
			StringBuilder sqlValues = new StringBuilder ();
			List<DbParameter> parameters = new List<DbParameter> ();
			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();

			PropertyInfo[] props = instance.GetType ().GetProperties (BindingFlags.Instance | BindingFlags.Public);
			if (props == null || props.Length == 0) {
				return false;
			}

			FieldMappingAttribute[] fmAttributes = null;
			foreach (PropertyInfo prop in props) {
				fmAttributes = prop.GetCustomAttributes (typeof(FieldMappingAttribute), true) as FieldMappingAttribute[];
				if (fmAttributes == null || fmAttributes.Length == 0 || fmAttributes[0].IgnoreWhenInsert) {
					continue;
				}

				if (sql.Length > 0) {
					sql.AppendLine (", " + prop.Name);
					sqlValues.AppendLine (", @" + prop.Name);
				} else {
					sql.AppendLine (prop.Name);
					sqlValues.AppendLine ("@" + prop.Name);
				}
				
				object value = prop.GetValue (instance, null);
				value = (value != null ? value : DBNull.Value);
				parameters.Add (DataAccessService.CreateDbParameter ("@" + prop.Name, fmAttributes[0].DbType, value));
			}

			if (sql.Length == 0) {
				return false;
			}

			sql.Insert (0, "Insert into " + tableName + "(");
			sql.AppendLine (")");
			sql.AppendLine ("Values(" + sqlValues.ToString () + ")");

			return das.ExecuteNonQuery (sql.ToString (), CommandType.Text, parameters.ToArray ()) > 0;
		}

		public static bool Update<T> (T instance) where T : new()
		{
			if (instance == null) {
				return false;
			}

			TableInfoAttribute[] attributes = instance.GetType ().GetCustomAttributes (typeof(TableInfoAttribute), true) as TableInfoAttribute[];
			if (attributes == null || attributes.Length == 0) {
				return false;
			}

			string tableName = attributes[0].TableName;
			if (string.IsNullOrEmpty (tableName)) {
				return false;
			}

			StringBuilder sql = new StringBuilder ();
			StringBuilder sqlWhere = new StringBuilder ();
			List<DbParameter> parameters = new List<DbParameter> ();
			DataAccessService das = DataAccessService.GetDataAccessServiceInstance ();

			PropertyInfo[] props = instance.GetType ().GetProperties (BindingFlags.Instance | BindingFlags.Public);
			if (props == null || props.Length == 0) {
				return false;
			}

			FieldMappingAttribute[] fmAttributes = null;
			foreach (PropertyInfo prop in props) {
				fmAttributes = prop.GetCustomAttributes (typeof(FieldMappingAttribute), true) as FieldMappingAttribute[];
				if (fmAttributes == null || fmAttributes.Length == 0) {
					continue;
				}

				if (!fmAttributes[0].IgnoreWhenUpdate) {
					if (sql.Length > 0) {
						sql.AppendLine (string.Format (", {0} = @{0}", prop.Name));
					} else {
						sql.AppendLine (string.Format ("set {0} = @{0}", prop.Name));
					}
				}

				if (fmAttributes[0].IsPrimaryKey) {
					if (sqlWhere.Length > 0) {
						sqlWhere.AppendLine (string.Format ("and {0} = @{0}", prop.Name));
					} else {
						sqlWhere.AppendLine (string.Format ("{0} = @{0}", prop.Name));
					}
				}

				object value = prop.GetValue (instance, null);
				value = (value != null ? value : DBNull.Value);
				
				parameters.Add (DataAccessService.CreateDbParameter ("@" + prop.Name, fmAttributes[0].DbType, value));
			}

			if (sql.Length == 0) {
				return false;
			}
			if (sqlWhere.Length == 0) {
				return false;
			}

			sql.Insert (0, "Update " + tableName + " ");
			sql.AppendLine ("Where " + sqlWhere.ToString ());

			return das.ExecuteNonQuery (sql.ToString (), CommandType.Text, parameters.ToArray ()) > 0;
		}

		#endregion
	}
}
